/**
  * This Procedure Closes the Sort Releases if their all the wafers gets the Binning Information.
  *
  */

ALTER PROCEDURE sp_close_sort_releases_based_on_bin_info 
AS
BEGIN

	DECLARE @sort_lot_id INT
	DECLARE @wafer_store_lot_id INT
	DECLARE @sort_process_step_id INT
	DECLARE @wafer_store_process_step_id INT
	DECLARE @lot_number VARCHAR(50)	
	DECLARE @SELECT_STRING NVARCHAR(200)
	
	DECLARE @sort_release_close_flag INT
	DECLARE @sort_rel_id INT
	DECLARE @wafer_ids VARCHAR(100)
	DECLARE @wafer_id VARCHAR(3)
	DECLARE @index INT

	DECLARE @wafers_with_bin_info_table TABLE ( Wafer_Id INT )

	DECLARE  @lots_with_bin_info_cursor CURSOR
	SET @lots_with_bin_info_cursor = CURSOR FOR SELECT DISTINCT Lot_Id FROM INFORMATIVE_BINNING --WHERE Bin_Type = 'Accept'
	OPEN @lots_with_bin_info_cursor
	FETCH NEXT FROM @lots_with_bin_info_cursor INTO @sort_lot_id
	WHILE @@FETCh_STATUS = 0
	BEGIN
		--Collecting the Wafers, for which Bin Info received.
		DELETE FROM @wafers_with_bin_info_table
		INSERT INTO @wafers_with_bin_info_table SELECT DISTINCT WAFER_ID FROM INFORMATIVE_BINNING WHERE LOT_ID = @sort_lot_id --AND Bin_Type = 'Accept'

		--Getting the Sort Process Step Id and Lot Number for the Lot Id.
		SELECT @sort_process_step_id = PROCESSSTEP_ID,  @lot_number = LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @sort_lot_id

		--Getting the Wafer Sort Process Step Id
		SELECT @wafer_store_process_step_id = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID( @sort_process_step_id ) WHERE Process_Type_Name = 'WAFER STORE'

		--SELECT @wafer_store_lot_id = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @wafer_store_process_step_id AND LOT_NUMBER = @lot_number
		SET @SELECT_STRING =N'declare tempCursor cursor for SELECT LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = ' + CAST(@wafer_store_process_step_id as VARCHAR)+ ' AND LOT_NUMBER like''' +@lot_number+'%''' 
		SET @wafer_store_lot_id = NULL
		EXECUTE SP_EXECUTESQL @SELECT_STRING 
 		OPEN tempCursor
		FETCH NEXT FROM tempCursor INTO @wafer_store_lot_id
		 
		CLOSE tempCursor
		DEALLOCATE tempCursor

		IF ( @wafer_store_lot_id IS NOT NULL ) 
		BEGIN

			DECLARE @sortReleaseCursor CURSOR
			SET @sortReleaseCursor = CURSOR FOR SELECT SR.Sort_Rel_Id, SRL.Wafer_Id FROM SORT_RELEASE_TABLE SR, SORT_RELEASE_LOT_TABLE SRL WHERE SRL.SORT_REL_ID = SR.SORT_REL_ID and STATUS = 'Approved' AND SRL.LOT_ID = @wafer_store_lot_id
			OPEN @sortReleaseCursor
			FETCH NEXT FROM @sortReleaseCursor INTO @sort_rel_id, @wafer_ids

 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				SET @sort_release_close_flag = 0 	--  0 means Need to Close Sort Release.
				SET @wafer_id = ''
				SET @index = 0				

				WHILE ( LEN(@wafer_ids) > 0 )
				BEGIN
					IF ( CHARINDEX ( ';', @wafer_ids, 1 ) > 0 ) 
					BEGIN
						SET @index = CHARINDEX ( ';', @wafer_ids, 1 )
						SET @wafer_id = SUBSTRING( @wafer_ids, 0, @index ) 
						SET @wafer_ids = SUBSTRING( @wafer_ids, @index+1, LEN( @wafer_ids ) )
						IF ( ( SELECT COUNT(*) FROM @wafers_with_bin_info_table WHERE Wafer_Id = @wafer_id ) = 0 )
						BEGIN
							SET @sort_release_close_flag = 1				
						END
					END	
				END
			
				--Close the Sort Release, as all of its wafer got the Binning Information.
				IF ( @sort_release_close_flag = 0 )
				BEGIN
					UPDATE SORT_RELEASE_TABLE SET STATUS = 'Closed' WHERE SORT_REL_ID = @sort_rel_id
				END
				SET @sort_rel_id = null
				SET @wafer_ids = null
				FETCH NEXT FROM @sortReleaseCursor INTO @sort_rel_id, @wafer_ids
			END
		END
	SET @sort_lot_id = null	
	FETCH NEXT FROM @lots_with_bin_info_cursor INTO @sort_lot_id
	END

END